CREATE TABLE jms_dm.dm_end_network_dispatch_detail_7not_dt(
    scansitecode varchar(64) NULL COMMENT "网点编码",
    store_code varchar(64) NULL COMMENT "门店code",
    business_id varchar(64) NULL COMMENT "品牌id",
    order_source_name varchar(64) NULL COMMENT "多平台订单来源名称",
    billcode varchar(64) NULL COMMENT "运单号",
    provider_id varchar(64) NULL COMMENT "省份id",
    provider_name varchar(64) NULL COMMENT "省份name",
    city_id varchar(64) NULL COMMENT "城市id",
    city_name varchar(128) NULL COMMENT "城市name",
    area_id varchar(64) NULL COMMENT "地区id",
    area_name varchar(64) NULL COMMENT "地区name",
    agent_code varchar(64) NULL COMMENT "代理区code",
    agent_name varchar(64) NULL COMMENT "代理区name",
    franchisee_code varchar(64) NULL COMMENT "加盟商code",
    franchisee_name varchar(128) NULL COMMENT "加盟商name",
    inputsite varchar(80) NULL COMMENT "网点名称",
    business_name varchar(64) NULL COMMENT "品牌name",
    store_name varchar(128) NULL COMMENT "门店name",
    store_address varchar(128) NULL COMMENT "门店详细地址",
    store_phone varchar(64) NULL COMMENT "门店电话",
    inputtime datetime NULL COMMENT "录入时间",
    scantime datetime NULL COMMENT "扫描时间",
    source varchar(32) NULL COMMENT "类型来源，机柜驿站",
    unique_store_id varchar(64) NULL COMMENT "门店唯一标识",
    update_time date NULL COMMENT "更新日期",
    scan_time_over datetime NULL COMMENT "完结件扫描时间",
    virt_code varchar(64) NULL COMMENT "虚拟代理区code",
    virt_name varchar(64) NULL COMMENT "虚拟代理区name",
    build_type varchar(64) NULL COMMENT "建设类型",
    deliver_scan_time datetime NULL COMMENT "派件时间",
    store_type varchar(64) NULL COMMENT "门店类型：校园驿站、乡镇驿站、城市驿站",
    is_store_over_8 tinyint(4) NULL COMMENT "8日未完结标识 1标识未完结0完结 ",
    is_store_over_9 tinyint(4) NULL COMMENT "9日未完结标识 1标识未完结0完结 ",
    is_store_over_10 tinyint(4) NULL COMMENT "10日未完结标识 1标识未完结0完结",
    is_store_over_12 tinyint(4) NULL COMMENT "12日未完结标识 1标识未完结0完结",
    is_store_over_15 tinyint(4) NULL COMMENT "15日未完结标识 1标识未完结0完结",
    is_store_over_30 tinyint(4) NULL COMMENT "30日未完结标识 1标识未完结0完结"
)ENGINE=OLAP
DUPLICATE KEY(scansitecode,store_code,business_id,order_source_name)
COMMENT "末端监控7日未出库——明细"
PARTITION BY RANGE(update_time) (
    START ("2023-08-01") END ("2023-10-20") EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(scansitecode) BUCKETS 10 
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-100",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "10",
"in_memory" = "false",
"storage_format" = "V2",
"enable_persistent_index" = "false"
);



alter table jms_dws.dws_network_end_dispatch_diff_rate add COLUMN(
 store_over_total_8       int comment'8日门店完结量'
,store_not_over_total_8   int comment'8日未完结量'
,store_over_total_9       int comment'9日门店完结量'
,store_not_over_total_9   int comment'9日未完结量'
,store_over_total_10      int comment'10日门店完结量'
,store_not_over_total_10  int comment'10门店未完结量'
,store_over_total_12      int comment'12日门店完结量'
,store_not_over_total_12  int comment'12日门店未完结量'
,store_over_total_15      int comment'15日完结量'
,store_not_over_total_15  int comment'15日未完结量'
,store_over_total_30      int comment'30日完结量'
,store_not_over_total_30  int comment'30日未完结量'
)
